-- @owner:@m0_68540079
-- @date:2025/4/21
-- @testpoint:to_timestamp在不同使用存储例程（Stored Routines）中的使用情况，expect：若入参1错误则入参2生效，若都错误，则合理报错
-- step1：在函数体中使用to_timestamp函数；expect：正常输出或合理报错
DROP FUNCTION IF EXISTS CALLFUNC;
CREATE OR REPLACE FUNCTION CALLFUNC (
    input_string IN VARCHAR2,
    DEFAULTVAL  IN INTEGER DEFAULT 123
) RETURN VARCHAR2
IS
BEGIN

RETURN TO_TIMESTAMP(input_string DEFAULT '20-Sep-01 01:00:00' ON CONVERSION ERROR,'YYYY-mon-DD HH24:MI:SS.FF');
END;
/

SELECT CALLFUNC('19-Sep-01 01:00:00');

DROP FUNCTION IF EXISTS CALLFUNC;

-- step2：在存储过程中使用to_timestamp函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS sp;
CREATE TABLE sp (
                    time timestamp
);

CREATE OR REPLACE PROCEDURE convert_TO_TIMESTAMP (
    p_input_number IN varchar  -- 输入数值
)
IS
BEGIN
INSERT INTO sp
SELECT TO_TIMESTAMP(p_input_number,'YYYY-mon-DD HH24:MI:SS.FF') ;
END;
/

CALL convert_TO_TIMESTAMP('20-Sep-01 01:00:00');

SELECT * FROM SP;

DROP TABLE IF EXISTS sp;

DROP PROCEDURE convert_to_timestamp(varchar);

-- step3：在package中使用to_timestamp函数；expect：正常输出或合理报错
DROP TABLE IF EXISTS sp;

DROP PACKAGE IF EXISTS string_operations;

CREATE TABLE sp (
                    time timestamp
);

CREATE OR REPLACE PACKAGE string_operations AS
    PROCEDURE convert_TO_TIMESTAMP (
        p_input_number IN varchar  -- 输入数值
    );
END string_operations;
/

CREATE OR REPLACE PACKAGE BODY string_operations AS
    PROCEDURE convert_TO_TIMESTAMP (
        p_input_number IN varchar
    ) IS
    BEGIN
INSERT INTO sp (time)
VALUES (TO_TIMESTAMP(p_input_number,'YYYY-mon-DD HH24:MI:SS.FF'));

COMMIT;
END convert_TO_TIMESTAMP;

BEGIN
NULL;
END string_operations;
/

BEGIN
string_operations.convert_TO_TIMESTAMP('20-Sep-01 01:00:00');
END;
/

SELECT * FROM sp;

DROP TABLE IF EXISTS sp;

DROP PACKAGE IF EXISTS string_operations;

-- step4：在触发器中使用to_timestamp函数；expect：正常输出或合理报错(触发器无法利用jdbc进行使用)
DROP table IF EXISTS test_logs;

DROP TRIGGER IF EXISTS trg_parse_time on test_logs;
CREATE TABLE test_logs (
                      id SERIAL PRIMARY KEY,
                      log_text TEXT,
                      raw_time TEXT,         -- 用户输入的时间字符串
                      parsed_time TIMESTAMP  -- 自动解析后的时间
);

CREATE OR REPLACE FUNCTION parse_time_function()
    RETURNS TRIGGER AS $$
BEGIN
    -- 使用 TO_TIMESTAMP() 将 raw_time 字段转换为 TIMESTAMP
    NEW.parsed_time := TO_TIMESTAMP(NEW.raw_time, 'YYYY-MM-DD HH24:MI');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_parse_time
    BEFORE INSERT ON test_logs
    FOR EACH ROW
EXECUTE FUNCTION parse_time_function();

INSERT INTO test_logs (log_text, raw_time) VALUES ('系统启动', '2025-06-11 10:00');
SELECT * FROM test_logs;

DROP table IF EXISTS test_logs;

DROP TRIGGER IF EXISTS trg_parse_time on test_logs;
